package aynu.hd.Admin.Controller;

import aynu.hd.Admin.Service.AdminRoomService;
import aynu.hd.pojo.Orders;
import aynu.hd.pojo.Room;
import aynu.hd.pojo.Schedule;
import aynu.hd.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;

@Controller
@RequestMapping("report")
public class reportFormController {
    @Autowired
    AdminRoomService adminRoomService;

    //备份数据库数据
    @RequestMapping("reportFormError")
    @ResponseBody
    public String backup() {
        try {
            System.out.println("开始备份........................................");
            File filePath = new File("C:\\sql");
            if (!filePath.exists()) {
                filePath.mkdirs();
            }
            Runtime rt = Runtime.getRuntime();
            // 调用 调用mysql的安装目录的命令
            Process child = rt.exec("D://Program Files (x86)//MySQL//MySQL Server 5.5//bin//mysqldump -h localhost -uroot -p123 hotel_management_system");
            // 设置导出编码为utf-8。这里必须是utf-8
            // 把进程执行中的控制台输出信息写入.sql文件，即生成了备份文件。注：如果不对控制台信息进行读出，则会导致进程堵塞无法运行
            InputStream in = child.getInputStream();// 控制台的输出信息作为输入流
            InputStreamReader xx = new InputStreamReader(in, "utf-8");
            // 设置输出流编码为utf-8。这里必须是utf-8，否则从流中读入的是乱码
            String inStr;
            StringBuffer sb = new StringBuffer("");
            String outStr;
            // 组合控制台输出信息字符串
            BufferedReader br = new BufferedReader(xx);
            while ((inStr = br.readLine()) != null) {
                sb.append(inStr + "\r\n");
            }
            outStr = sb.toString();
            // 要用来做导入用的sql目标文件：
            FileOutputStream fout = new FileOutputStream(
                    "c:/sql/hotel_management_system.sql");
            OutputStreamWriter writer = new OutputStreamWriter(fout, "utf-8");
            writer.write(outStr);
            writer.flush();
            in.close();
            xx.close();
            br.close();
            writer.close();
            fout.close();
            System.out.println("备份完成........................................");
        } catch (Exception e) {
            e.printStackTrace();
            return "FAIL";
        }
        return "OK";
    }

    //导出订单报表
    @RequestMapping("reportFormOrder")
    public void reportFormOrder(HttpServletResponse response) {
        try {
            List<Schedule> scheduleList = adminRoomService.scheduleListYes();
            //创建excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();//创建一张表
            HSSFRow titleRow = sheet.createRow(0);//创建第一行，起始为0
            titleRow.createCell(0).setCellValue("订单id");
            titleRow.createCell(1).setCellValue("房间id");//第一列
            titleRow.createCell(2).setCellValue("房间名称地址");
            titleRow.createCell(3).setCellValue("房间品质");
            titleRow.createCell(4).setCellValue("订单开始时间");
            titleRow.createCell(5).setCellValue("订单结束时间");
            titleRow.createCell(6).setCellValue("入住人员身份证");
            titleRow.createCell(7).setCellValue("入住人员名字");
            titleRow.createCell(8).setCellValue("入住人员性别");
            titleRow.createCell(9).setCellValue("操作人");
            titleRow.createCell(10).setCellValue("预定房间数量");
            titleRow.createCell(11).setCellValue("订单创建日期");
            titleRow.createCell(12).setCellValue("金额");
            int cell = 1;
            double d = 0f;
            for (Schedule schedule : scheduleList) {
                HSSFRow row = sheet.createRow(cell);//从第二行开始保存数据
                row.createCell(0).setCellValue(schedule.getOrders().getOrders_id().toString());
                row.createCell(1).setCellValue(schedule.getRoom().getRoom_id().toString());//将数据库的数据遍历出来
                row.createCell(2).setCellValue(schedule.getRoom().getRoom_name());
                row.createCell(3).setCellValue(schedule.getRoom().getRoom_quality());
                row.createCell(4).setCellValue(schedule.getOrders().getOrders_start_date().toString());
                row.createCell(5).setCellValue(schedule.getOrders().getOrders_end_date().toString());
                row.createCell(6).setCellValue(schedule.getOrders().getOrders_identity_card());
                row.createCell(7).setCellValue(schedule.getOrders().getOrders_name());
                row.createCell(8).setCellValue(schedule.getOrders().getOrders_sex());
                row.createCell(9).setCellValue(schedule.getUser().getUser_truename());
                row.createCell(10).setCellValue(schedule.getSchedule_amount());
                row.createCell(11).setCellValue(schedule.getOrders().getOrders_time().toString());
                row.createCell(12).setCellValue(schedule.getOrders().getOrders_money().toString());
                d = d + schedule.getOrders().getOrders_money();
                cell++;
            }
            HSSFRow row = sheet.createRow(cell);
            row.createCell(0).setCellValue("总盈利金额:" + d);
            String fileName = "订单统计表.xls";
            //设置ContentType请求信息格式
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //导出用户报表
    @RequestMapping("reportFormUser")
    public void reportFormUser(HttpServletResponse response) {
        try {
            List<User> userList = adminRoomService.userList();
            //创建excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();//创建一张表
            HSSFRow titleRow = sheet.createRow(0);//创建第一行，起始为0
            titleRow.createCell(0).setCellValue("用户id");
            titleRow.createCell(1).setCellValue("账户");//第一列
            titleRow.createCell(2).setCellValue("密码");
            titleRow.createCell(3).setCellValue("身份证");
            titleRow.createCell(4).setCellValue("性别");
            titleRow.createCell(5).setCellValue("真实姓名");
            titleRow.createCell(6).setCellValue("家庭住址");
            titleRow.createCell(7).setCellValue("联系方式");
            titleRow.createCell(8).setCellValue("电子邮件");
            titleRow.createCell(9).setCellValue("收藏房间id");
            titleRow.createCell(10).setCellValue("用户类别");
            titleRow.createCell(11).setCellValue("创建时间");
            int cell = 1;
            for (User user : userList) {
                HSSFRow row = sheet.createRow(cell);//从第二行开始保存数据
                row.createCell(0).setCellValue(user.getUser_id().toString());
                row.createCell(1).setCellValue(user.getUser_name());//将数据库的数据遍历出来
                row.createCell(2).setCellValue(user.getUser_password());
                row.createCell(3).setCellValue(user.getUser_identity_card());
                row.createCell(4).setCellValue(user.getUser_sex());
                row.createCell(5).setCellValue(user.getUser_truename());
                row.createCell(6).setCellValue(user.getUser_address());
                row.createCell(7).setCellValue(user.getUser_phone());
                row.createCell(8).setCellValue(user.getUser_email());
                row.createCell(9).setCellValue(user.getUser_collect());
                row.createCell(10).setCellValue(user.getUser_admin());
                row.createCell(11).setCellValue(user.getUser_creat_time().toString());
                cell++;
            }
            String fileName = "用户统计表.xls";
            //设置ContentType请求信息格式
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //导出房间报表
    @RequestMapping("reportFormRoom")
    public void reportFormRoom(HttpServletResponse response) {
        try {
            List<Room> roomList = adminRoomService.queryRoomAll();
            //创建excel文件
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet();//创建一张表
            HSSFRow titleRow = sheet.createRow(0);//创建第一行，起始为0
            titleRow.createCell(0).setCellValue("房间标识");
            titleRow.createCell(1).setCellValue("房间名称(地址)");//第一列
            titleRow.createCell(2).setCellValue("房间品质");
            titleRow.createCell(3).setCellValue("房间描述");
            titleRow.createCell(4).setCellValue("原价");
            titleRow.createCell(5).setCellValue("现价");
            titleRow.createCell(6).setCellValue("优惠力度");
            titleRow.createCell(7).setCellValue("建议入住人数");
            titleRow.createCell(8).setCellValue("房间数量");
            titleRow.createCell(9).setCellValue("被收藏的数量");
            titleRow.createCell(10).setCellValue("入住数量(总数)");
            int cell = 1;
            for (Room room : roomList) {
                HSSFRow row = sheet.createRow(cell);//从第二行开始保存数据
                row.createCell(0).setCellValue(room.getRoom_id().toString());
                row.createCell(1).setCellValue(room.getRoom_name());//将数据库的数据遍历出来
                row.createCell(2).setCellValue(room.getRoom_quality());
                row.createCell(3).setCellValue(room.getRoom_describe());
                row.createCell(4).setCellValue(room.getRoom_original_price().toString());
                row.createCell(5).setCellValue(room.getRoom_current_price().toString());
                row.createCell(6).setCellValue(room.getRoom_preferential_price().toString());
                row.createCell(7).setCellValue(room.getRoom_size().toString());
                row.createCell(8).setCellValue(room.getRoom_amount().toString());
                row.createCell(9).setCellValue(room.getRoom_collect().toString());
                row.createCell(10).setCellValue(room.getRoom_live().toString());
                cell++;
            }
            String fileName = "所有房间统计表.xls";
            //设置ContentType请求信息格式
            response.setContentType("application/ms-excel;charset=UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            OutputStream outputStream = response.getOutputStream();
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
